﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using System.Configuration;
namespace AcctrueWMS.Foundation.DAL
{
    class OracleOperator : IDBOperator
    {
        private const string strQueryTablesSQL = "select   table_name   as tableName  from user_tables  ";     //获取系统中的用户表
        private const string strQueryCloumnsSQL = "  SELECT column_Name as  NAME,data_length as  length,'' as DataType from   user_tab_columns   ";     //获表的列信息
        private static readonly String connString =DBFactory.ConnString;
        #region  获取数据库时间
        public DateTime GetDBDateTime()
        {
            DateTime rtnDateTime = DateTime.Now;
            string GetDateTimeSQL = "Select   sysdate   from   dual ";
            try
            {
                rtnDateTime = Convert.ToDateTime(OracleHelper.ExecuteScalar(connString, System.Data.CommandType.Text, GetDateTimeSQL));
            }
            catch (Exception)
            { }
            return rtnDateTime;
        }

        #endregion

        #region 获取数据库中所有用户表
        public DataTable GetAllTables(string condition)
        {
            DataTable allTables = new DataTable();
            string queryTableSQL = strQueryTablesSQL;
            if (condition != null && condition.Trim() != "")
            {
                queryTableSQL += string.Format("  where  (upper(table_name) like '%{0}%')  ", condition.Trim().ToUpper());
            }
            using (allTables = OracleHelper.ExecuteDataset(connString, System.Data.CommandType.Text, queryTableSQL).Tables[0])
                return allTables;
        }

        #endregion

        #region  获取表的列信息
        public DataTable GetColumnsInfo(string tableName)
        {
            DataTable allColumnsInfo = new DataTable();
            string queryColumnsSQL = strQueryCloumnsSQL;

            if (tableName != null && tableName.Trim() != "")
            {
                queryColumnsSQL += string.Format("  where  (upper(table_name)='{0}')  ", tableName.Trim().ToUpper());
            }
            else
            {
                queryColumnsSQL += " where 1=2 ";
            }
            using (allColumnsInfo = OracleHelper.ExecuteDataset(connString, System.Data.CommandType.Text, queryColumnsSQL).Tables[0])
                return allColumnsInfo;
        }

        #endregion

        #region 保存DateTables
        public string SaveTables(TableModel[] tableModels, string mode)
        {
            string rtnKeyValues = string.Empty;
            if (tableModels.Length > 0)
            {
                DataTable mainTable = tableModels[0].DataTable;
                string mainTableName = mainTable.TableName;
                string[] keyNames = tableModels[0].KeyName.Split(',');        //可能出现联合主键
                rtnKeyValues = GetKeyValues(mainTable, keyNames);           //获取联合主键值          
                string commitSQL = string.Empty;
                string saveDetailSQL = string.Empty;
                string[] strExecSQL;
                using (OracleConnection oracleConn = new OracleConnection(connString))
                {
                    if (oracleConn.State != ConnectionState.Open) oracleConn.Open();
                    OracleTransaction transaction = oracleConn.BeginTransaction(IsolationLevel.ReadCommitted);
                    //新增
                    if (mode == "1")
                    {
                        commitSQL = GetInsertSQL(mainTable, "", "").Replace("r\n", " ").Replace(";", "");     //oracle 在一个sql语段里面不识别 r\n
                        WMSqlExcuter.ExecuteNonQuery(transaction, commitSQL, CommandType.Text);
                        if (string.IsNullOrEmpty(rtnKeyValues))
                        {
                            for (int i = 1; i < tableModels.Length; i++)
                            {
                                if (tableModels[i] == null) continue;
                                string ForeignkeyName = tableModels[i].ForeignName;
                                saveDetailSQL = GetInsertSQL(tableModels[i].DataTable, ForeignkeyName, rtnKeyValues);
                                WMSqlExcuter.ExecuteNonQuery(transaction, saveDetailSQL, CommandType.Text);
                            }
                        }
                    }
                    else  //修改
                    {
                        commitSQL = GetUpdateSQL(mainTable, keyNames, null, "");
                        WMSqlExcuter.ExecuteNonQuery(commitSQL);
                        if (!string.IsNullOrEmpty(rtnKeyValues))
                        {
                            saveDetailSQL = GetUpdateSQL(tableModels[0].DataTable, keyNames, null, "");
                            string ForignKeyValues = GetKeyValues(tableModels[0].DataTable, keyNames);
                            for (int i = 1; i < tableModels.Length; i++)    //如果有多个从表，则依次保存
                            {
                                string ForeignkeyName = tableModels[i].ForeignName;
                                string DetailkeyName = tableModels[i].KeyName;
                                saveDetailSQL += GetUpdateSQL(tableModels[i].DataTable, null, ForeignkeyName, ForignKeyValues);
                            }
                            if (!string.IsNullOrEmpty(saveDetailSQL))
                            {
                                //oracle不支持多条sql语句一起提交，所以只能单条提交                
                                strExecSQL = saveDetailSQL.Split(new String[] { ";" + System.Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
                                for (int intSQLSect = 0; intSQLSect < strExecSQL.Length; intSQLSect++)
                                {
                                    WMSqlExcuter.ExecuteNonQuery(transaction, strExecSQL[intSQLSect], CommandType.Text);
                                }
                            }
                        }
                    }

                    transaction.Commit();
                    if (oracleConn.State != ConnectionState.Closed) oracleConn.Close();
                }
            }
            return rtnKeyValues;
        }
        //新增的sql语句
        private string GetInsertSQL(DataTable dt, string foreignKeyName, string foreignKeyValue)
        {
            string saveSQL = string.Empty;
            string mainTableName = dt.TableName;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                saveSQL += string.Format("  insert into   {0} (", mainTableName);
                //获取所有列名称
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    saveSQL += dt.Columns[j].ColumnName.ToString() + "  , ";
                }
                saveSQL = string.Format("{0} ) values (", saveSQL.Substring(0, saveSQL.Length - 1));
                //获取所有列值
                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    string Value = dt.Rows[i][k].ToString();
                    if (!string.IsNullOrEmpty(foreignKeyName) && !string.IsNullOrEmpty(foreignKeyValue))   //如果含有外键，则处理外键取值
                    {
                        Value = GetForeignKeyValue(Value, dt.Columns[k].ColumnName, foreignKeyName, foreignKeyValue);
                    }
                    if (dt.Columns[k].DataType.Name == "DateTime")
                    {
                        saveSQL += string.Format(" to_date('{0}','yyyy-mm-dd hh24:mi:ss') ,", Value);
                    }
                    else
                    {
                        saveSQL += string.Format("'{0}'", Value);
                    }
                }
                saveSQL = saveSQL.Substring(0, saveSQL.Length - 1) + " ) ;" + System.Environment.NewLine;
            }
            return saveSQL;
        }

        //修改的sql语句
        private string GetUpdateSQL(DataTable dt, string[] keyName, string foreignKeyName, string foreignKeyValue)
        {
            string saveSQL = string.Empty, keyValue = string.Empty;
            string tableName = dt.TableName;
            if (foreignKeyName == "")  //更新主表信息
            {
                saveSQL = string.Format("update  {0}  set   ", tableName);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (!CheckContainsItem(dt.Columns[j].ColumnName, keyName))  //排除主键值
                    {
                        if (dt.Columns[j].DataType.Name == "DateTime")
                        {
                            saveSQL += string.Format(" to_date('{0}','yyyy-mm-dd hh24:mi:ss') ,", dt.Rows[0][j].ToString());   //处理日期类型
                        }
                        else
                        {
                            saveSQL += string.Format(" {0}='{1}' ,", dt.Columns[j].ColumnName.ToString(), dt.Rows[0][j].ToString());
                        }
                    }
                }
                saveSQL = string.Format("{0} where 1=1   {1}  ; ", saveSQL.Substring(0, saveSQL.Length - 1), MakeKeyName_Value(dt, keyName));
            }
            else  //对明细表的操作：先删除，在插入
            {
                saveSQL = string.Format("delete from {0}  where 1=1  {1}  ;", tableName, CreateKeyValuesSQL(foreignKeyName, foreignKeyValue)) + System.Environment.NewLine;
                saveSQL += GetInsertSQL(dt, foreignKeyName, foreignKeyValue);
            }
            return saveSQL;
        }

        //如果列名属于外键，则返回外键值
        private string GetForeignKeyValue(string defaleValue, string ColumnName, string foreignkeyName, string foreignKeyValue)
        {
            string value = defaleValue;
            string[] arrayForeignkeyName = foreignkeyName.Split(',');
            string[] arrayForeignKeyValue = foreignKeyValue.Split(',');
            if (arrayForeignkeyName.Length != arrayForeignKeyValue.Length) return value;  //如果外键列数不相等，则直接返回
            for (int m = 0; m < arrayForeignkeyName.Length; m++)
            {
                if (ColumnName.ToUpper() == arrayForeignkeyName[m].ToUpper())       //如果是外键，则值为 ForeignKeyValue
                {
                    value = arrayForeignKeyValue[m];
                    break;
                }
            }
            return value;
        }

        //生成主键名和值的表达式
        private string CreateKeyValuesSQL(string names, string values)
        {
            string express = string.Empty;
            string[] arrayNames = names.Split(',');
            string[] arrayValues = values.Split(',');
            if (arrayNames.Length != arrayValues.Length) return " and  (1=2) ";  //列名和值数目不匹配
            for (int i = 0; i < arrayNames.Length; i++)
            {
                express += string.Format("  and ( {0}='{1}' ) ", arrayNames[i], arrayValues[i]);
            }
            return express;
        }

        //把表的主键名和值相匹配
        private string MakeKeyName_Value(DataTable dt, string[] keyNames)
        {
            string express = string.Empty;
            if (keyNames.Length < 0) return "and (1=2)";
            foreach (string KeyName in keyNames)
            {
                if (dt.Columns.Contains(KeyName))
                {
                    express += string.Format("  and ( {0}='{1}' ) ", KeyName, dt.Rows[0]["KeyName"]);
                }
            }
            return express;
        }

        //判断数组中是否包含指定字符串
        private bool CheckContainsItem(string express, string[] array)
        {
            if (array.Length <= 0) return false;
            foreach (string item in array)
            {
                if (item.ToLower().Contains(express.ToLower()))
                {
                    return true;
                }
            }
            return false;
        }

        //获取联合主键所有值
        private string GetKeyValues(DataTable dt, string[] KeyNames)
        {
            string KeyValues = string.Empty;
            foreach (string keyName in KeyNames)                            //  获取主表主键值 做从表外键
            {
                KeyValues += dt.Rows[0][keyName].ToString() + ",";
            }
            if (!string.IsNullOrEmpty(KeyValues))
            {
                KeyValues = KeyValues.Substring(0, KeyValues.Length - 1);
            }
            return KeyValues;
        }

        #endregion

        #region  获取流水号后自增列编码
        public int GetSequenceNo(string tableName, string colTag)
        {
            if (string.IsNullOrEmpty(colTag)) colTag = "main"; //给默认值
            int iRtn = 0;
            DateTime nowData = GetDBDateTime();
            using (OracleConnection oracleConn = new OracleConnection(connString))
            {
                if (oracleConn.State != ConnectionState.Open)
                {
                    oracleConn.Open();
                }
                OracleTransaction transaction = oracleConn.BeginTransaction(IsolationLevel.ReadCommitted);
                string getSeqNoSQL = string.Format("select   seqNo from sys_sequenceNo where tableName='{0}' and  createDate= to_date('{1}','yyyy-mm-dd')   and colTag='{2}'   ", tableName.Trim(), nowData.ToShortDateString().ToString(), colTag.Trim());
                object value = WMSqlExcuter.ExecuteScalar(transaction, getSeqNoSQL, CommandType.Text);

                string seqNo = string.Empty;
                if (value != null)
                {
                    seqNo = value.ToString();
                }
                string updateSQL = string.Empty;
                if (!string.IsNullOrEmpty(seqNo))
                {
                    iRtn = int.Parse(seqNo) + 1;
                    updateSQL = string.Format(" update  sys_sequenceNo set  seqNo={0}  where  tableName='{1}' and colTag='{2}'  and   createDate= to_date('{3}','yyyy-mm-dd')  ", iRtn, tableName.Trim(), colTag, nowData.ToShortDateString().ToString());
                }
                else
                {
                    iRtn = 1;
                    updateSQL = string.Format(" insert into   sys_sequenceNo (tableName,colTag,createDate,seqNo) values ('{0}','{1}', to_date('{2}','yyyy-mm-dd') , {3}) ", tableName.Trim(), colTag.Trim(), nowData.ToShortDateString().ToString(), iRtn);
                }
                WMSqlExcuter.ExecuteNonQuery(updateSQL);
                transaction.Commit();
                if (oracleConn.State != ConnectionState.Closed)
                {
                    oracleConn.Close();
                }
            }
            return iRtn;
        }
        #endregion

        #region 执行事务
        public int ExecuteTran(string cmdText)
        {
            string[] strExecSQL;
            using (OracleConnection oracleConn = new OracleConnection(connString))
            {
                if (oracleConn.State != ConnectionState.Open)
                {
                    oracleConn.Open();
                }
                OracleTransaction transaction = oracleConn.BeginTransaction(IsolationLevel.ReadCommitted);
                strExecSQL = cmdText.Split(new String[] { ";" }, StringSplitOptions.RemoveEmptyEntries);   //oracle不支持多条sql语句一起提交，所以只能单条提交
                for (int intSQLSect = 0; intSQLSect < strExecSQL.Length; intSQLSect++)
                {
                    WMSqlExcuter.ExecuteNonQuery(strExecSQL[intSQLSect]);
                }
                transaction.Commit();
                if (oracleConn.State != ConnectionState.Closed)
                {
                    oracleConn.Close();
                }
                return 1;
            }
        }
        #endregion

    }
}
